﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using DevExpress.XtraEditors.Controls;

namespace ChinaDynastyTours.Controls
{
    class ConsoleCtrl
    {
        public static DataTable GetScheduleData(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            //formatSql.AppendFormat(@"SELECT * FROM sys_Schedule where (CONVERT(varchar(100), ReminderDate, 23)=(Select CONVERT(varchar(100), GETDATE(), 23))) AND username='{0}'", username);
            formatSql.AppendFormat(@"SELECT * FROM sys_Scheduling where username='{0}'", username);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetConsoleOperationData(DateTime time)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT * FROM vw_TourGroupSchedule WHERE  (StartDate='{0}' OR EndDate='{0}') AND Date='{0}'", time.ToString("yyyy-MM-dd"));
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable GetConsoleTrainData(DateTime time)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT * FROM vw_ConsoleTrain WHERE StartDate='{0}' AND OrderState='{1}'", time.AddDays(1).ToString("yyyy-MM-dd"),OrderStateEnum.未订);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable GetConsoleFlightData(DateTime time)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT * FROM vw_ConsoleFlight WHERE StartDate='{0}' AND OrderState='{1}'", time.AddDays(1).ToString("yyyy-MM-dd"), OrderStateEnum.未订);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetConsoleVehicleData(DateTime time)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            //获取提前3天的未定汽车信息
            formatSql.AppendFormat(@"SELECT * FROM vw_ConsoleVehicle WHERE DATEDIFF(day,'{0}',ServiceDate)<=3  AND OrderState='{1}'", time.AddDays(1).ToString("yyyy-MM-dd"), OrderStateEnum.未订);
            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
    }
}
